﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using VgoCardChange.Models.Objects;
using VgoCardChange.Models.DAL;

namespace VgoCardChange.Models
{
    public class NewsModels
    {
        public List<News> GetNews()
        {
            List<News> listnews = new List<News>();
            ConnectDB con = new ConnectDB();
            try
            {
                string sql = "select NewsID,NewsName,DatePost,Content,FullName,CateName from TblNews join TblCate on TblNews.CateID=TblCate.CateID join TblUser on TblNews.UserID=TblUser.UserID";
                con.ExeReader(sql);
                while (con.dataRead.Read())
                {
                    News news = new News();
                    news.NewsID = int.Parse(con.dataRead["NewsID"].ToString());
                    news.NewsName = con.dataRead["NewsName"].ToString();
                    news.DatePost =DateTime.Parse(con.dataRead["DatePost"].ToString());
                    news.Content = con.dataRead["Content"].ToString();
                    news.FullName = con.dataRead["FullName"].ToString();
                    news.CateName = con.dataRead["CateName"].ToString();
                    listnews.Add(news);
                }
            }
            catch (Exception ex)
            { Console.Write(ex); }
            finally { con.CloseData(); con = null; }
            return listnews;
        }
        public void CreateNew(News news)
        {
            ConnectDB con = new ConnectDB();
            try
            {
                string sql = "sp_CreateNews @NewsName=N'" + news.NewsName + "', @Content=N'" + news.Content + "', @DatePost='" + news.DatePost + "', @CateID='" + news.CateID + "' , @UserID='" + news.UserID + "' ";
                con.ExeNonQuery(sql);
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            finally { con.CloseData(); con = null; }
        }
        public void EditNews(News news)
        {
            ConnectDB con = new ConnectDB();
            try
            {
                string sql = "EditNews @NewsID='"+news.NewsID+"' , @NewsName=N'"+news.NewsName+"', @Content=N'"+news.Content+"', @DatePost='"+news.DatePost+"', @CateID='"+news.CateID+"' , @UserID='"+news.UserID+"'";
                con.ExeNonQuery(sql);
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            finally { con.CloseData(); con = null; }

        }
        public void DeleteNews(int NewsID)
        {
            ConnectDB con = new ConnectDB();
            try
            {
                string sql = "sp_DeleteNews @NewsID='" + NewsID + "'";
                con.ExeNonQuery(sql);
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            finally { con.CloseData(); con = null; }
        }
        public List<News> GetNewsID(int NewsID)
        {
            List<News> listnews = new List<News>();
            ConnectDB con = new ConnectDB();
            try
            {
                string sql = "sp_GetNewsID @NewsID='" + NewsID + "'";
                con.ExeReader(sql);
                while (con.dataRead.Read())
                {
                    News news = new News();
                    news.NewsID = int.Parse(con.dataRead["NewsID"].ToString());
                    news.NewsName = con.dataRead["NewsName"].ToString();
                    news.DatePost = DateTime.Parse(con.dataRead["DatePost"].ToString());
                    news.Content = con.dataRead["Content"].ToString();
                    news.UserID = int.Parse(con.dataRead["UserID"].ToString());
                    news.CateID = int.Parse(con.dataRead["CateID"].ToString());
                    listnews.Add(news);
                }
            }
            catch (Exception ex)
            { Console.Write(ex); }
            finally { con.CloseData(); con = null; }
            return listnews;

        }
        public int GetUserID(string UserName)
        {
            int UserID = 0;
            ConnectDB con=new ConnectDB();
            try
            {
                string sql = "select TblUser.UserID from TblUser where UserName=N'" + UserName + "'";
                con.ExeReader(sql);
                while (con.dataRead.Read())
                {
                    UserID =int.Parse(con.dataRead["UserID"].ToString());
                    return UserID;
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex);
            }
            finally { con.CloseData(); con = null; }
            return UserID;
        }
    }
}